package com.lh.pospi.x_store.utils;

import com.google.gson.Gson;
import com.google.gson.reflect.TypeToken;
import com.lh.pospi.x_store.Interface.StatusListener;
import com.lh.pospi.x_store.app.MyApp;
import com.lh.pospi.x_store.modle.BillBean;
import com.lh.pospi.x_store.modle.BrandBean;
import com.lh.pospi.x_store.modle.CatBean;
import com.lh.pospi.x_store.modle.CusBean;
import com.lh.pospi.x_store.modle.GoodsBean;
import com.lh.pospi.x_store.modle.MarktBean;
import com.lh.pospi.x_store.modle.SupBean;
import com.lh.pospi.x_store.modle.UserBean;
import com.vondear.rxtool.RxLogTool;
import com.vondear.rxtool.RxTimeTool;
import com.vondear.rxtool.RxTool;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

public class DBService {
    private Connection conn=null; //打开数据库对象
    private PreparedStatement ps=null;//操作整合sql语句的对象
    private ResultSet rs=null;//查询结果的集合
    //DBService 对象
    public static DBService dbService=null;

    /**
     * 构造方法 私有化
     * */

    private DBService(){

    }

    /**
     * 获取MySQL数据库单例类对象
     * */

    public static DBService getDbService(){
        if(dbService==null){
            dbService=new DBService();
        }
        return dbService;
    }

    public interface ConnectListner {
        void s();

        void f(String msg);
    }
    public void login(ConnectListner listner) {
        DBOpenHelper.getConnReady(listner);
    }

    /**
     * 拉取商品信息
     */
    public void saveGoods() {
        List<GoodsBean> beans = new ArrayList<>();
        String sql = "select item_no as code,item_subno as barcode," +
                "item_name as name,unit_no as unit," +
                "item_size as spec, " +
                "item_supcust as sup,item_clsno as cat," +
                "item_brand as brand,base_price jj,sale_price lsj," +
                "sale_price as pfj,1 jhl,unit_no as junit," +
                "item_rem as memo from t_bd_item_info where status = '1'";
        conn = DBOpenHelper.getConn();
        try {
            if(conn!=null&&(!conn.isClosed())){
                ps= (PreparedStatement) conn.prepareStatement(sql);
                if(ps!=null){
                    rs= ps.executeQuery();
                    if(rs!=null){
                        while(rs.next()){
//                            RxLogTool.w(rs.getString("name")+"--------"+rs.getString("code"));
                            GoodsBean u=new GoodsBean();
                            u.setCode(rs.getString("code").trim());
                            u.setNum(1);
                            u.setBarcode(rs.getString("barcode"));
                            u.setName(rs.getString("name"));
                            u.setUnit(rs.getString("unit"));
                            u.setSpec(rs.getString("spec"));
                            u.setSup(rs.getString("sup"));
                            u.setCat(rs.getString("cat"));
                            u.setBrand(rs.getString("brand"));
                            u.setJj(rs.getString("jj").trim());
                            u.setLsj(rs.getString("lsj").trim());
                            u.setPfj(rs.getString("pfj").trim());
                            beans.add(u);
                        }
                    }
                }
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        if (beans.size() > 0) {
            MyApp.getInstance().getDaoSession().getGoodsBeanDao().deleteAll();
            MyApp.getInstance().getDaoSession().getGoodsBeanDao().insertInTx(beans);
        }
        RxLogTool.w("商品下载完成，共："+beans.size()+"条");
        DBOpenHelper.closeAll(conn,ps,rs);//关闭相关操作
    }
    /**
     * 拉取门店信息
     */
    public void saveMark() {
        List<MarktBean> beans = new ArrayList<>();
        String sql = "select branch_no as code,branch_name as name from t_bd_branch_info";
        conn = DBOpenHelper.getConn();
        try {
            if(conn!=null&&(!conn.isClosed())){
                ps= (PreparedStatement) conn.prepareStatement(sql);
                if(ps!=null){
                    rs= ps.executeQuery();
                    if(rs!=null){
                        while(rs.next()){
                            RxLogTool.w(rs.getString("name")+"--------"+rs.getString("code"));
                            MarktBean u=new MarktBean();
                            u.setCode(rs.getString("code").trim());
                            u.setName(rs.getString("name").trim());
                            beans.add(u);
                        }
                    }
                }
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        if (beans.size() > 0) {
            MyApp.getInstance().getDaoSession().getMarktBeanDao().deleteAll();
            MyApp.getInstance().getDaoSession().getMarktBeanDao().insertInTx(beans);
        }
        RxLogTool.w("门店下载完成，共："+beans.size()+"条");
        DBOpenHelper.closeAll(conn,ps,rs);//关闭相关操作
    }
    /**
     * 拉取品牌信息
     */
    public void saveBrand() {
        List<BrandBean> beans = new ArrayList<>();
        String sql = "select item_brandno as code,item_brandname as name from t_bd_item_brand";
        conn = DBOpenHelper.getConn();
        try {
            if(conn!=null&&(!conn.isClosed())){
                ps= (PreparedStatement) conn.prepareStatement(sql);
                if(ps!=null){
                    rs= ps.executeQuery();
                    if(rs!=null){
                        while(rs.next()){
//                            RxLogTool.w(rs.getString("name")+"--------"+rs.getString("code"));
                            BrandBean u=new BrandBean();
                            u.setCode(rs.getString("code"));
                            u.setName(rs.getString("name"));
                            beans.add(u);
                        }
                    }
                }
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        if (beans.size() > 0) {
            MyApp.getInstance().getDaoSession().getBrandBeanDao().deleteAll();
            MyApp.getInstance().getDaoSession().getBrandBeanDao().insertInTx(beans);
        }
        RxLogTool.w("品牌下载完成，共："+beans.size()+"条");
        DBOpenHelper.closeAll(conn,ps,rs);//关闭相关操作
    }
    /**
     * 拉取类别信息
     */
    public void saveCat() {
        List<CatBean> beans = new ArrayList<>();
        String sql = "select item_clsno as code,item_clsname as name from t_bd_item_cls";
        conn = DBOpenHelper.getConn();
        try {
            if(conn!=null&&(!conn.isClosed())){
                ps= (PreparedStatement) conn.prepareStatement(sql);
                if(ps!=null){
                    rs= ps.executeQuery();
                    if(rs!=null){
                        while(rs.next()){
//                            RxLogTool.w(rs.getString("name")+"--------"+rs.getString("code"));
                            CatBean u=new CatBean();
                            u.setCode(rs.getString("code"));
                            u.setName(rs.getString("name"));
                            beans.add(u);
                        }
                    }
                }
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        if (beans.size() > 0) {
            MyApp.getInstance().getDaoSession().getCatBeanDao().deleteAll();
            MyApp.getInstance().getDaoSession().getCatBeanDao().insertInTx(beans);
        }
        RxLogTool.w("类别下载完成，共："+beans.size()+"条");
        DBOpenHelper.closeAll(conn,ps,rs);//关闭相关操作
    }
    /**
     * 拉取供应商信息
     */
    public void saveSup() {
        List<SupBean> beans = new ArrayList<>();
        String sql = "select supcust_no as code,sup_name as name,sup_addr as addr,sup_tel as tel,memo as memo from t_bd_supcust_info where sup_type = '1'";
        conn = DBOpenHelper.getConn();
        try {
            if(conn!=null&&(!conn.isClosed())){
                ps= (PreparedStatement) conn.prepareStatement(sql);
                if(ps!=null){
                    rs= ps.executeQuery();
                    if(rs!=null){
                        while(rs.next()){
//                            RxLogTool.w(rs.getString("name")+"--------"+rs.getString("code"));
                            SupBean u=new SupBean();
                            u.setCode(rs.getString("code"));
                            u.setName(rs.getString("name"));
                            beans.add(u);
                        }
                    }
                }
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        if (beans.size() > 0) {
            MyApp.getInstance().getDaoSession().getSupBeanDao().deleteAll();
            MyApp.getInstance().getDaoSession().getSupBeanDao().insertInTx(beans);
        }
        RxLogTool.w("供应商下载完成，共："+beans.size()+"条");
        DBOpenHelper.closeAll(conn,ps,rs);//关闭相关操作
    }
    /**
     * 拉取客户信息
     */
    public void saveCus() {
        List<CusBean> beans = new ArrayList<>();
        String sql = "select supcust_no as code,sup_name as name,sup_addr as addr,sup_tel as tel,memo as memo from t_bd_supcust_info where sup_type = '2'";
        conn = DBOpenHelper.getConn();
        try {
            if(conn!=null&&(!conn.isClosed())){
                ps= (PreparedStatement) conn.prepareStatement(sql);
                if(ps!=null){
                    rs= ps.executeQuery();
                    if(rs!=null){
                        while(rs.next()){
//                            RxLogTool.w(rs.getString("name")+"--------"+rs.getString("code"));
                            CusBean u=new CusBean();
                            u.setCode(rs.getString("code"));
                            u.setName(rs.getString("name"));
                            beans.add(u);
                        }
                    }
                }
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        if (beans.size() > 0) {
            MyApp.getInstance().getDaoSession().getCusBeanDao().deleteAll();
            MyApp.getInstance().getDaoSession().getCusBeanDao().insertInTx(beans);
        }
        RxLogTool.w("客户下载完成，共："+beans.size()+"条");
        DBOpenHelper.closeAll(conn,ps,rs);//关闭相关操作
    }
    /**
     * 拉取客户信息
     */
    public void saveUser() {
        List<UserBean> beans = new ArrayList<>();
        String sql = "select oper_id as id,oper_id as code,oper_name as name,oper_pw as password from t_sys_operator";
        conn = DBOpenHelper.getConn();
        try {
            if(conn!=null&&(!conn.isClosed())){
                ps= (PreparedStatement) conn.prepareStatement(sql);
                if(ps!=null){
                    rs= ps.executeQuery();
                    if(rs!=null){
                        while(rs.next()){
                            RxLogTool.w(rs.getString("name")+"--------"+rs.getString("code"));
                            UserBean u=new UserBean();
                            u.setCode(rs.getString("code").trim());
                            u.setName(rs.getString("name").trim());
                            beans.add(u);
                        }
                    }
                }
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        if (beans.size() > 0) {
            MyApp.getInstance().getDaoSession().getUserBeanDao().deleteAll();
            MyApp.getInstance().getDaoSession().getUserBeanDao().insertInTx(beans);
        }
        RxLogTool.w("用户下载完成，共："+beans.size()+"条");
//        DBOpenHelper.closeAll(conn,ps,rs);//关闭相关操作
    }

    public void upBill(BillBean bean , StatusListener listener) {
        List<GoodsBean> goodsBeans = new Gson().fromJson(bean.getBilldetail(), new TypeToken<List<GoodsBean>>() {
        }.getType());
        double amount = 0;
        for (GoodsBean goodsBean : goodsBeans) {
            amount += goodsBean.getNum() * Double.parseDouble(goodsBean.getLsj());
        }
        String date = RxTimeTool.getCurrentDateTime("yyyy-MM-dd HH:mm:ss");
        String time = RxTimeTool.getCurrentDateTime("yyMMdd");

        String getNo = "select ( isnull ( sheet_value , 0 ) + 1 ) from t_sys_sheet_no where lower ( sheet_id ) =lower ( 'CR' )";
        String billNo = "";

        conn = DBOpenHelper.getConn();
        try {
            if(conn!=null&&(!conn.isClosed())){
                int index = 0;

                //-------------------获取序号，生产单据号-------------------------
                ps = conn.prepareStatement(getNo);
                rs = ps.executeQuery();
                rs.next();
                index = rs.getInt(1);
                //生成单据号
                billNo = "CR"+bean.getMarket().substring(0,3)
                        +time
                        +String.format("%04d", index);
                //--------------------------------------------


                //---------------------更新序号-----------------------
                conn.prepareStatement("update t_sys_sheet_no set sheet_value ="+index+" where sheet_id ='CR'").executeUpdate();//更新序号
                //--------------------------------------------


                //---------------------获取系统单据号-----------------------
                rs = conn.prepareStatement("select max(sheet_no) from t_im_check_init").executeQuery();
                rs.next();
                String sheetNo = rs.getString(1).trim();
                //--------------------------------------------


                //----------------------插入单据头----------------------
                String head = "INSERT INTO t_im_check_master " +
                        "( check_no, sheet_no, trans_no, oper_id, " +
                        "oper_date, branch_no, approve_flag, " +
                        "oper_range, dup_process, check_cls, sheet_amt ) " +
                        "VALUES ( '"+sheetNo+"', '"+billNo+"', 'CR', " +
                        "'1001', '"+date+"', '"+bean.getMarket()+"', " +
                        "'0', '0', '1', '"+bean.getBillno()+"', "+Util.formatNum(amount)+" )";
                RxLogTool.w("序号："+index);
                RxLogTool.w("单据号："+billNo);
                RxLogTool.w("系统号：" + sheetNo);
                RxLogTool.w("单据头sql："+head);
                conn.prepareStatement(head).execute();
                //--------------------------------------------


                //---------------------插入单据详情-----------------------
                for (GoodsBean g : goodsBeans) {
                    RxLogTool.w(g.getCode()+"--"+g.getJj()+"---"+g.getLsj());
                    String detail = "INSERT INTO t_im_check_detail ( item_no, in_price, sale_price, sheet_no,real_qty,recheck_qty,memo ) " +
                            "VALUES ( '" + g.getCode() + "', " + Double.parseDouble(g.getJj()) + ", " + Double.parseDouble(g.getLsj()) +
                            ", '"+billNo+"',"+g.getNum()+",0 ,'' )";

                    conn.prepareStatement(detail).execute();
                }
                //--------------------------------------------

                bean.setStatus("1");
                MyApp.getInstance().getDaoSession().getBillBeanDao().update(bean);
                listener.upSuccess("成功");


            }
        } catch (SQLException e) {
            e.printStackTrace();
            listener.upFailed("异常");
        }
        DBOpenHelper.closeAll(conn,ps,rs);//关闭相关操作
    }
}
